-- 创建项目状态枚举类型
CREATE TYPE project_status AS ENUM ('draft', 'submitted', 'under_review', 'approved', 'rejected');

-- 创建项目表
CREATE TABLE IF NOT EXISTS projects (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    short_description VARCHAR(500),
    
    -- 关联信息
    competition_id BIGINT NOT NULL REFERENCES competitions(id) ON DELETE CASCADE,
    team_id BIGINT REFERENCES teams(id) ON DELETE SET NULL,
    creator_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    
    -- 项目内容
    repository_url VARCHAR(500),
    demo_url VARCHAR(500),
    documentation_url VARCHAR(500),
    
    -- 技术栈和标签
    technologies JSONB DEFAULT '[]'::jsonb,
    tags JSONB DEFAULT '[]'::jsonb,
    
    -- 文件和媒体
    files JSONB DEFAULT '[]'::jsonb,
    images JSONB DEFAULT '[]'::jsonb,
    video_url VARCHAR(500),
    
    -- 评分和排名
    score NUMERIC(5, 2),
    ranking INTEGER,
    judges_scores JSONB DEFAULT '{}'::jsonb,
    
    -- 状态和元数据
    status project_status DEFAULT 'draft',
    is_public BOOLEAN DEFAULT true,
    view_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    
    -- 提交信息
    submitted_at TIMESTAMP WITH TIME ZONE,
    submission_notes TEXT,
    
    -- 时间戳
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建项目评论表
CREATE TABLE IF NOT EXISTS project_comments (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    
    -- 评论类型
    is_judge_comment BOOLEAN DEFAULT false,
    rating INTEGER CHECK (rating >= 1 AND rating <= 5),
    
    -- 时间戳
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_projects_competition_id ON projects(competition_id);
CREATE INDEX IF NOT EXISTS idx_projects_team_id ON projects(team_id);
CREATE INDEX IF NOT EXISTS idx_projects_creator_id ON projects(creator_id);
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
CREATE INDEX IF NOT EXISTS idx_projects_is_public ON projects(is_public);
CREATE INDEX IF NOT EXISTS idx_projects_score ON projects(score);
CREATE INDEX IF NOT EXISTS idx_projects_ranking ON projects(ranking);
CREATE INDEX IF NOT EXISTS idx_projects_created_at ON projects(created_at);
CREATE INDEX IF NOT EXISTS idx_projects_submitted_at ON projects(submitted_at);

CREATE INDEX IF NOT EXISTS idx_project_comments_project_id ON project_comments(project_id);
CREATE INDEX IF NOT EXISTS idx_project_comments_user_id ON project_comments(user_id);
CREATE INDEX IF NOT EXISTS idx_project_comments_is_judge_comment ON project_comments(is_judge_comment);
CREATE INDEX IF NOT EXISTS idx_project_comments_created_at ON project_comments(created_at);

-- 为项目表创建更新时间触发器
DROP TRIGGER IF EXISTS update_projects_updated_at ON projects;
CREATE TRIGGER update_projects_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 为项目评论表创建更新时间触发器
DROP TRIGGER IF EXISTS update_project_comments_updated_at ON project_comments;
CREATE TRIGGER update_project_comments_updated_at
    BEFORE UPDATE ON project_comments
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 创建更新用户项目数量的函数
CREATE OR REPLACE FUNCTION update_user_projects_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users SET projects_created = projects_created + 1 WHERE id = NEW.creator_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users SET projects_created = projects_created - 1 WHERE id = OLD.creator_id;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ language 'plpgsql';

-- 创建用户项目数量更新触发器
DROP TRIGGER IF EXISTS update_user_projects_count_trigger ON projects;
CREATE TRIGGER update_user_projects_count_trigger
    AFTER INSERT OR DELETE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION update_user_projects_count();

-- 启用 RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_comments ENABLE ROW LEVEL SECURITY;

-- 项目表策略
DROP POLICY IF EXISTS "Anyone can view public projects" ON projects;
CREATE POLICY "Anyone can view public projects" ON projects
    FOR SELECT USING (is_public = true OR creator_id = (auth.uid()::text)::bigint OR EXISTS (
        SELECT 1 FROM team_members 
        WHERE team_members.team_id = projects.team_id 
        AND team_members.user_id = (auth.uid()::text)::bigint
    ));

DROP POLICY IF EXISTS "Project creators can update projects" ON projects;
CREATE POLICY "Project creators can update projects" ON projects
    FOR UPDATE USING (creator_id = (auth.uid()::text)::bigint OR EXISTS (
        SELECT 1 FROM team_members 
        WHERE team_members.team_id = projects.team_id 
        AND team_members.user_id = (auth.uid()::text)::bigint
        AND team_members.role IN ('leader', 'member')
    ));

DROP POLICY IF EXISTS "Authenticated users can create projects" ON projects;
CREATE POLICY "Authenticated users can create projects" ON projects
    FOR INSERT WITH CHECK (auth.role() = 'authenticated' AND creator_id = (auth.uid()::text)::bigint);

DROP POLICY IF EXISTS "Project creators can delete projects" ON projects;
CREATE POLICY "Project creators can delete projects" ON projects
    FOR DELETE USING (creator_id = (auth.uid()::text)::bigint);

-- 项目评论表策略
DROP POLICY IF EXISTS "Anyone can view comments on public projects" ON project_comments;
CREATE POLICY "Anyone can view comments on public projects" ON project_comments
    FOR SELECT USING (EXISTS (
        SELECT 1 FROM projects 
        WHERE projects.id = project_comments.project_id 
        AND (projects.is_public = true OR projects.creator_id = (auth.uid()::text)::bigint)
    ));

DROP POLICY IF EXISTS "Authenticated users can create comments" ON project_comments;
CREATE POLICY "Authenticated users can create comments" ON project_comments
    FOR INSERT WITH CHECK (auth.role() = 'authenticated' AND user_id = (auth.uid()::text)::bigint);

DROP POLICY IF EXISTS "Comment authors can update comments" ON project_comments;
CREATE POLICY "Comment authors can update comments" ON project_comments
    FOR UPDATE USING (user_id = (auth.uid()::text)::bigint);

DROP POLICY IF EXISTS "Comment authors can delete comments" ON project_comments;
CREATE POLICY "Comment authors can delete comments" ON project_comments
    FOR DELETE USING (user_id = (auth.uid()::text)::bigint);

-- 授权
GRANT SELECT ON projects TO anon;
GRANT ALL PRIVILEGES ON projects TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE projects_id_seq TO authenticated;

GRANT SELECT ON project_comments TO anon;
GRANT ALL PRIVILEGES ON project_comments TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE project_comments_id_seq TO authenticated;

-- 插入测试项目数据
INSERT INTO projects (title, description, competition_id, team_id, creator_id, technologies, tags, repository_url, demo_url, status) VALUES
('智能推荐系统', '基于机器学习的个性化推荐算法，提升用户体验', 1, 1, 1, '["Python", "TensorFlow", "Flask", "Redis"]', '["AI", "推荐系统", "机器学习"]', 'https://github.com/team1/recommendation-system', 'https://demo.recommendation-system.com', 'submitted'),
('全栈电商平台', '现代化的电商解决方案，支持多种支付方式', 2, 2, 3, '["React", "Node.js", "MongoDB", "Stripe"]', '["电商", "全栈", "支付"]', 'https://github.com/team2/ecommerce-platform', 'https://ecommerce-demo.com', 'under_review'),
('去中心化投票系统', '基于区块链的透明投票平台', 3, 3, 4, '["Solidity", "Web3.js", "React", "IPFS"]', '["区块链", "投票", "去中心化"]', 'https://github.com/team3/voting-dapp', 'https://voting-dapp.com', 'approved'),
('健康追踪应用', '帮助用户监控和改善健康状况的移动应用', 4, 4, 5, '["React Native", "Firebase", "HealthKit", "Chart.js"]', '["健康", "移动应用", "数据可视化"]', 'https://github.com/team4/health-tracker', 'https://health-tracker-demo.com', 'submitted'),
('数据可视化仪表板', '企业级数据分析和可视化平台', 5, 5, 2, '["Python", "Plotly", "Dash", "PostgreSQL"]', '["数据分析", "可视化", "仪表板"]', 'https://github.com/team5/data-dashboard', 'https://data-dashboard-demo.com', 'draft');

-- 插入测试项目评论数据
INSERT INTO project_comments (project_id, user_id, content, rating) VALUES
(1, 2, '非常出色的推荐算法实现，用户体验很棒！', 5),
(1, 3, '代码质量很高，文档也很详细', 4),
(2, 1, '电商平台功能完整，界面设计现代化', 5),
(2, 4, '支付集成做得很好，安全性考虑周全', 4),
(3, 5, '区块链技术应用得当，投票过程透明可信', 5),
(4, 1, '健康数据可视化做得很棒，对用户很有帮助', 4),
(5, 3, '数据分析功能强大，图表展示清晰', 4);